package com.javaweb.dao.impl;

import com.javaweb.dao.ArticleDao;
import com.javaweb.model.Article;
import com.javaweb.model.Tag;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.ObjectUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/10/9 9:51
 */

@Repository
public class ArticleDaoImpl implements ArticleDao  {

    @Autowired
    JdbcTemplate jdbcTemplate ;


    @Override
    public int insert(Article article) {
        // Assert.notNull(article)

        String sql = "INSERT INTO tb_article (user_id, title, content, status, " +
                "description, category_id, create_time, update_time)" +
                " VALUES (?,?,?,?,?,?,?,?)" ;
        Object [] args = new Object[]{
                article.getUserId(),
                article.getTitle(),
                article.getContent(),
                article.getStatus(),
                article.getDescription(),
                article.getCategoryId(),
                new java.sql.Date(article.getCreateTime().getTime()),
                new java.sql.Date(article.getUpdateTime().getTime())}                               ;
        return jdbcTemplate.update(sql, args);
    }

    @Override
    public int updateContent(Integer id, String newContent) {
        // 检查id和newContent是否为空
        if (id == null || newContent == null) {
            throw new IllegalArgumentException("id and newContent cannot be null");
        }

        // SQL 更新语句
        String sql = "UPDATE tb_article SET content = ?, update_time = ? WHERE id = ?";

        // 参数数组，包含新的内容、当前时间以及文章ID
        Object[] args = new Object[]{
                newContent,
                new java.sql.Timestamp(System.currentTimeMillis()), // 更新内容时更新更新时间
                id
        };

        // 更新数据库内容并返回受影响的行数
        int rowsAffected = jdbcTemplate.update(sql, args);

        // 如果更新失败返回0，更新成功返回1
        return rowsAffected > 0 ? 1 : 0;
    }

    @Override
    public int updateTitle(Integer id, String newTitle) {
        // 检查id和newContent是否为空
        if (id == null || newTitle == null) {
            throw new IllegalArgumentException("id and newContent cannot be null");
        }

        // SQL 更新语句
        String sql = "UPDATE tb_article SET title = ?, update_time = ? WHERE id = ?";

        // 参数数组，包含新的内容、当前时间以及文章ID
        Object[] args = new Object[]{
                newTitle,
                new java.sql.Timestamp(System.currentTimeMillis()), // 更新内容时更新更新时间
                id
        };

        // 更新数据库内容并返回受影响的行数
        int rowsAffected = jdbcTemplate.update(sql, args);

        // 如果更新失败返回0，更新成功返回1
        return rowsAffected > 0 ? 1 : 0;
    }

    @Override
    public int deleteById(Integer id) {
        // 检查id和newContent是否为空
        if (id == null ) {
            throw new IllegalArgumentException("id and newContent cannot be null");
        }

        // SQL 更新语句
        String sql = "UPDATE tb_article SET title = ?WHERE id = ?";

        // 参数数组，包含新的内容、当前时间以及文章ID
        Object[] args = new Object[]{
               " ",
                id
        };

        // 更新数据库内容并返回受影响的行数
        int rowsAffected = jdbcTemplate.update(sql, args);

        // 如果更新失败返回0，更新成功返回1
        return rowsAffected > 0 ? 1 : 0;
    }

    @Override
    public List<Article> selectByUserId(Integer userId) {
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose),0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE a.user_id = ?" ;

        return jdbcTemplate.query(sql, new Object[]{userId},
                rs -> {
            Map<Integer, Article> articleMap = new HashMap<>();
            while (rs.next()) {
                Integer articleId = rs.getInt("id");
                Article article = articleMap.get(articleId);
                if (ObjectUtils.isEmpty(article)) {
                    article = new Article();
                    article.setId(articleId);
                    article.setTitle(rs.getString("title"));
                    article.setContent(rs.getString("content"));
                    article.setCreateTime(rs.getDate("create_time"));
                    article.setUpdateTime(rs.getDate("update_time"));
                    article.setTags(new ArrayList<>());
                    articleMap.put(articleId, article);
                }

                Integer tagId = rs.getInt("tag_id");
                if (tagId != null) {
                    Tag tag = new Tag();
                    tag.setId(tagId);
                    tag.setName(rs.getString("tag_name"));
                    tag.setCreateTime(rs.getDate("tag_create_time"));
                    tag.setUpdateTime(rs.getDate("tag_update_time"));
                    article.getTags().add(tag);
                }
                article.setSupportCount(rs.getInt("sup")) ;
                article.setOpposeCount(rs.getInt("opp")) ;
            }
            return new ArrayList<>(articleMap.values());
        });
    }


    @Override
    public int selectId(Integer userId, String title, Date createTime) {
        return 0;
    }

    @Override
    public List<Article> selectByKeyword(String keyword) {
        String sql = "SELECT * FROM tb_article WHERE title LIKE ? OR content LIKE ?" ;
        return jdbcTemplate.query(sql,new Object[]{"%" + keyword + "%"},
                new RowMapper<Article>() {
                    @Override
                    public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Article article = new Article();
                        article.setId(rs.getInt("id"));
                        article.setTitle(rs.getString("title"));
                        article.setContent(rs.getString("content"));
                        article.setStatus(rs.getInt("status")) ;
                        article.setDescription(rs.getString("description")) ;
                        article.setCategoryId(rs.getInt("category_id")) ;
                        article.setCreateTime(rs.getDate("create_time")) ;
                        article.setUpdateTime(rs.getDate("update_time")) ;
                        return article;

                    }
                });
    }
    @Override
    public List<Article> selectByTimePeriod(Date startTime, Date endTime) {
        // 构建基础 SQL 语句
        StringBuilder sql = new StringBuilder("SELECT * FROM tb_article WHERE 1=1");

        // 存储查询参数
        List<Object> params = new ArrayList<>();

        // 如果 startTime 不为空，添加查询条件
        if (startTime != null) {
            sql.append(" AND create_time >= ?");
            params.add(new java.sql.Timestamp(startTime.getTime()));
        }

        // 如果 endTime 不为空，添加查询条件
        if (endTime != null) {
            sql.append(" AND create_time <= ?");
            params.add(new java.sql.Timestamp(endTime.getTime()));
        }

        // 按照创建时间降序排序
        sql.append(" ORDER BY create_time DESC");

        // 执行查询并映射结果
        List<Article> articles = jdbcTemplate.query(sql.toString(), params.toArray(), new RowMapper<Article>() {
            @Override
            public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
                Article article = new Article();
                article.setId(rs.getInt("id"));
                article.setUserId(rs.getInt("user_id"));
                article.setTitle(rs.getString("title"));
                article.setContent(rs.getString("content"));
                article.setStatus(rs.getInt("status"));
                article.setDescription(rs.getString("description"));
                article.setCategoryId(rs.getInt("category_id"));
                article.setCreateTime(rs.getTimestamp("create_time"));
                article.setUpdateTime(rs.getTimestamp("update_time"));
                return article;
            }
        });

        // 返回结果：如果查询为空，则返回 null，否则返回文章列表
        return articles.isEmpty() ? null : articles;
    }

    @Override
    public List<Article> selectByTag(Integer tagId) {
        String sql = "SELECT * FROM tb_article t1 \n" +
                "LEFT JOIN tb_article_tag t2 \n" +
                "ON t1.id = t2.article_id \n" +
                "WHERE t2.tag_id = ?";

        List<Article> articles = jdbcTemplate.query(sql, new Object[]{tagId}, new BeanPropertyRowMapper<>(Article.class));
        return articles ;
    }
@Override
public List<Article> selectByCategory(Integer categoryId) {
    // 检查 categoryId 是否为空
    if (categoryId == null) {
        throw new IllegalArgumentException("CategoryId cannot be null");
    }

    // SQL 查询语句
    String sql = "SELECT * FROM tb_article WHERE category_id = ? ORDER BY create_time DESC";

    // 查询结果并将其映射到 Article 对象
    List<Article> articles = jdbcTemplate.query(sql, new Object[]{categoryId}, new RowMapper<Article>() {
        @Override
        public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setUserId(rs.getInt("user_id"));
            article.setTitle(rs.getString("title"));
            article.setContent(rs.getString("content"));
            article.setStatus(rs.getInt("status"));
            article.setDescription(rs.getString("description"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCreateTime(rs.getTimestamp("create_time"));
            article.setUpdateTime(rs.getTimestamp("update_time"));
            return article;
        }
    });

    // 返回结果：如果查询为空，则返回 null，否则返回文章列表
    return articles.isEmpty() ? null : articles;
}


}
